package com.logica.mdm.webservices.messages;

/**
 * Constants Utility class
 */
public final class SearchPartyByMdmIdConstants {

	
	/** Search Party By Mdm Id Query */
	public static final String PARTY_SEARCH_QUERY = "SELECT pkg_ws_party_search.rowid_object,"
				+ " pkg_ws_party_search.party_type,"
				+ " pkg_ws_party_search.party_type_disp,"
				+ " pkg_ws_party_search.gender_cd,"
				+ " pkg_ws_party_search.gender_disp,"
				+ " pkg_ws_party_search.legal_form_cd,"
				+ " pkg_ws_party_search.libelle_legal_form,"
				+ " pkg_ws_party_search.name_prefix_cd,"
				+ " pkg_ws_party_search.name_prefix_disp,"
				+ " pkg_ws_party_search.last_name,"
				+ " pkg_ws_party_search.first_name,"
				+ " pkg_ws_party_search.birthdate,"
				+ " pkg_ws_party_search.marital_status,"
				+ " pkg_ws_party_search.marital_status_type_disp,"
				+ " pkg_ws_party_search.number_of_children,"
				+ " pkg_ws_party_search.social_category,"
				+ " pkg_ws_party_search.social_category_label,"
				+ " pkg_ws_party_search.tax_id,"
				+ " pkg_ws_party_search.home_phone_number,"
				+ " pkg_ws_party_search.cell_phone_number,"
				+ " pkg_ws_party_search.work_phone_number,"
				+ " pkg_ws_party_search.fax_phone_number,"
				+ " pkg_ws_party_search.flag_partenaire,"
				+ " pkg_ws_party_search.debranchement_sigeo"
				+ " FROM ( SELECT /*+ LEADING (ic_party) */"
		      			+ " ic_party.rowid_object,"
				      	+ " ic_party.party_type,"
				      	+ " c_lu_party_type.party_type_disp,"
				      	+ " ic_party.gender_cd,"
				      	+ " c_lu_gender.gender_disp,"
				      	+ " ic_party.legal_form_cd,"
				      	+ " c_lu_party_legal_form.libelle_legal_form,"
				      	+ " ic_party.name_prefix_cd,"
				      	+ " c_lu_name_prefix.name_prefix_disp,"
				      	+ " ic_party.last_name,"
				      	+ " ic_party.first_name,"
				      	+ " c_person_details.birthdate,"
				      	+ " c_person_details.marital_status,"
				      	+ " c_lu_marital_status.marital_status_type_disp,"
				      	+ " c_person_details.number_of_children,"
				      	+ " c_person_details.social_category,"
				      	+ " c_lu_social_category.social_category_label,"
				      	+ " ic_party.tax_id,"
				      	+ " partyphone_dom AS home_phone_number,"
				        + " partyphone_por AS cell_phone_number,"
				        + " partyphone_bur AS work_phone_number,"
				        + " partyphone_fax AS fax_phone_number,"
				      	+ " ic_party.flag_partenaire,"
				      	+ " ic_party.debranchement_sigeo"
				      	+ " FROM ( SELECT c_party.rowid_object,"
								+ " c_party.party_type,"
								+ " c_party.gender_cd,"
								+ " c_party.legal_form_cd,"
								+ " c_party.name_prefix_cd,"
								+ " c_party.last_name,"
								+ " c_party.first_name,"
								+ " c_party.flag_partenaire,"
								+ " c_party.debranchement_sigeo,"
								+ " c_party.tax_id"
								+ " FROM c_party"
								+ " WHERE c_party.rowid_object = RPAD(:mdmId, 14, ' ')"
						+ " ) ic_party"
						+ " LEFT OUTER JOIN c_person_details ON ic_party.rowid_object = c_person_details.party_id"
						+ " LEFT OUTER JOIN c_lu_party_legal_form ON ic_party.legal_form_cd = c_lu_party_legal_form.type_legal_form" 
						+ " LEFT OUTER JOIN c_lu_gender ON ic_party.gender_cd = c_lu_gender.gender_code" 
						+ " LEFT OUTER JOIN c_lu_party_type ON ic_party.party_type = c_lu_party_type.party_type"
						+ " LEFT OUTER JOIN c_lu_name_prefix ON ic_party.name_prefix_cd = c_lu_name_prefix.name_prefix_code" 
						+ " LEFT OUTER JOIN c_lu_marital_status ON c_person_details.marital_status = c_lu_marital_status.marital_status_code" 
						+ " LEFT OUTER JOIN c_lu_social_category ON c_person_details.social_category = c_lu_social_category.social_category"
						+ " LEFT OUTER JOIN ("
							+ " SELECT party_id as partyphone_partyid, bur as partyphone_bur, dom as partyphone_dom, por as partyphone_por, fax as partyphone_fax"
					    	+ " FROM("
								+ " SELECT party_id, phone_number, phone_type FROM c_party_phone)" 
							+ " PIVOT ( MAX(phone_number) FOR PHONE_TYPE IN ('BUR' AS bur, 'DOM' AS dom, 'POR' AS por, 'FAX' AS fax)))"
							+ " ON partyphone_partyid = ic_party.rowid_object"
						+ " ) pkg_ws_party_search";

	/**
	 * SELECT contract number and contract office from partyId.
	 */
	/*public static final String PARTY_LIST_ADDRESS_SEARCH_QUERY = "SELECT v_party_address.address_type_desc,"
			+ " v_party_address.address_type,"
			+ " v_party_address.street_number,"
			+ " v_party_address.street_type,"
			+ " v_party_address.street_name,"
			+ " v_party_address.address_line_2,"
			+ " v_party_address.address_line_3,"
			+ " v_party_address.mail_box_number,"
			+ " v_party_address.address_line_5,"
			+ " v_party_address.city_name,"
			+ " v_party_address.postal_cd,"
			+ " v_party_address.country_name,"
			+ " v_party_address.country_code,"
			+ " v_party_address.hexacle_voie,"
			+ " v_party_address.hexacle_localite,"
			+ " v_party_address.party_id"
			+ " FROM PKG_WS_PARTY_ADDRESS v_party_address"
			+ " WHERE v_party_address.party_id IN (:partyIds)";*/
	public static final String PARTY_LIST_ADDRESS_SEARCH_QUERY = "SELECT C_LU_ADDRESS_TYPE.ADDRESS_TYPE_DESC address_type_desc,"
			+ " C_PARTY_ADDRESS_REL.ADDRESS_TYPE address_type,"
			+ " C_ADDRESS.STREET_NUMBER street_number,"
			+ " C_ADDRESS.STREET_TYPE street_type,"
			+ " C_ADDRESS.STREET_NAME street_name,"
			+ " C_ADDRESS.ADDRESS_LINE_2 address_line_2,"
			+ " C_ADDRESS.ADDRESS_LINE_3 address_line_3,"
			+ " C_ADDRESS.MAIL_BOX_NUMBER mail_box_number,"
			+ " C_ADDRESS.ADDRESS_LINE_5 address_line_5,"
			+ " C_ADDRESS.CITY_NAME city_name,"
			+ " C_ADDRESS.POSTAL_CD postal_cd,"
			+ " C_ADDRESS.COUNTRY_NAME country_name,"
			+ " C_ADDRESS.COUNTRY_CODE country_code,"
			+ " C_ADDRESS.HEXACLE_VOIE hexacle_voie,"
			+ " C_ADDRESS.HEXACLE_LOCALITE hexacle_localite,"
			+ " C_PARTY_ADDRESS_REL.PARTY_ID party_id"
			+ " FROM CMX_ORS.C_PARTY_ADDRESS_REL C_PARTY_ADDRESS_REL"
			+ "  INNER JOIN CMX_ORS.C_ADDRESS C_ADDRESS"
	        + "     ON C_ADDRESS.ROWID_OBJECT = C_PARTY_ADDRESS_REL.ADDRESS_ID"
	        + "  INNER JOIN CMX_ORS.C_LU_ADDRESS_TYPE C_LU_ADDRESS_TYPE"
	        + "     ON C_LU_ADDRESS_TYPE.ADDRESS_TYPE = C_PARTY_ADDRESS_REL.ADDRESS_TYPE"
	        + "     where C_PARTY_ADDRESS_REL.Party_Id IN (:partyIds)";
	
	
	
	/** Search Address By Party Mdm Id Query */
	/*public static final String ADDRESS_SEARCH_QUERY = "SELECT v_party_address.address_type_desc,"
			+ " v_party_address.address_type,"
			+ " v_party_address.street_number,"
			+ " v_party_address.street_type,"
			+ " v_party_address.street_name,"
			+ " v_party_address.address_line_2,"
			+ " v_party_address.address_line_3,"
			+ " v_party_address.mail_box_number,"
			+ " v_party_address.address_line_5,"
			+ " v_party_address.city_name,"
			+ " v_party_address.postal_cd,"
			+ " v_party_address.country_name,"
			+ " v_party_address.country_code,"
			+ " v_party_address.hexacle_voie,"
			+ " v_party_address.hexacle_localite,"
			+ " v_party_address.party_id"
			+ " FROM PKG_WS_PARTY_ADDRESS v_party_address"
			+ " WHERE v_party_address.party_id = :partyId";*/
	public static final String ADDRESS_SEARCH_QUERY = "SELECT C_LU_ADDRESS_TYPE.ADDRESS_TYPE_DESC address_type_desc,"
			+ " C_PARTY_ADDRESS_REL.ADDRESS_TYPE address_type,"
			+ " C_ADDRESS.STREET_NUMBER street_number,"
			+ " C_ADDRESS.STREET_TYPE street_type,"
			+ " C_ADDRESS.STREET_NAME street_name,"
			+ " C_ADDRESS.ADDRESS_LINE_2 address_line_2,"
			+ " C_ADDRESS.ADDRESS_LINE_3 address_line_3,"
			+ " C_ADDRESS.MAIL_BOX_NUMBER mail_box_number,"
			+ " C_ADDRESS.ADDRESS_LINE_5 address_line_5,"
			+ " C_ADDRESS.CITY_NAME city_name,"
			+ " C_ADDRESS.POSTAL_CD postal_cd,"
			+ " C_ADDRESS.COUNTRY_NAME country_name,"
			+ " C_ADDRESS.COUNTRY_CODE country_code,"
			+ " C_ADDRESS.HEXACLE_VOIE hexacle_voie,"
			+ " C_ADDRESS.HEXACLE_LOCALITE hexacle_localite,"
			+ " C_PARTY_ADDRESS_REL.PARTY_ID party_id"
			+ " FROM CMX_ORS.C_PARTY_ADDRESS_REL C_PARTY_ADDRESS_REL"
			+ "  INNER JOIN CMX_ORS.C_ADDRESS C_ADDRESS"
	        + "     ON C_ADDRESS.ROWID_OBJECT = C_PARTY_ADDRESS_REL.ADDRESS_ID"
	        + "  INNER JOIN CMX_ORS.C_LU_ADDRESS_TYPE C_LU_ADDRESS_TYPE"
	        + "     ON C_LU_ADDRESS_TYPE.ADDRESS_TYPE = C_PARTY_ADDRESS_REL.ADDRESS_TYPE"
	        + "     where C_PARTY_ADDRESS_REL.Party_Id = :partyId";
	
	
	/** Search PartyRel By Party Mdm Id Query */
	/*public static final String PARTY_REL_SEARCH_QUERY = "SELECT v_party_rel.rowid_object,"
			+ " v_party_rel.rowid_rel_type,"
			+ " v_party_rel.party_id1,"
			+ " v_party_rel.party_id2,"
			+ " v_party_rel.rel_name,"
			+ " v_party_rel.rel_desc,"
			+ " v_party_rel.ocod_account_number"
			+ " FROM PKG_WS_PARTY_REL v_party_rel"
			+ " WHERE v_party_rel.party_id1 = :partyId1";
			//+ " OR v_party_rel.party_id2 = :partyId2";
			 */
	public static final String PARTY_REL_SEARCH_QUERY = "SELECT C_PARTY_REL.ROWID_OBJECT rowid_object,"
			+ "     C_PARTY_REL.ROWID_REL_TYPE rowid_rel_type,"
			+ "     C_PARTY_REL.PARTY_ID1 party_id1,"
			+ "     C_PARTY_REL.PARTY_ID2 party_id2,"
			+ "     C_PARTY_REL.REL_NAME rel_name,"
			+ "     C_PARTY_REL.REL_DESC rel_desc,"
			+ "     C_PARTY_REL.OCOD_ACCOUNT_NUMBER ocod_account_number"
			+ " FROM CMX_ORS.C_PARTY_REL"
			+ " WHERE C_PARTY_REL.party_id1 = :partyId1";
	
	/**
	 * SELECT electronic address from partyId.
	 */
	public static final String PARTY_LIST_ELECTRONIC_ADDRESS_SEARCH_QUERY = "SELECT electronic_address, electronic_address_type, party_id"
			+ " FROM CMX_ORS.C_PARTY_ELECT_AD"
			+ " WHERE party_id IN (:partyIds)";
	
	/**
	 * SELECT electronic address from partyId.
	 */
	public static final String PARTY_ELECTRONIC_ADDRESS_SEARCH_QUERY = "SELECT electronic_address, electronic_address_type, party_id"
			+ " FROM CMX_ORS.C_PARTY_ELECT_AD"
			+ " WHERE party_id = :partyId";	
	
	public static final String PARTY_ELECTRONIC_ADDRESS_PRO_SEARCH_QUERY = "SELECT electronic_address, electronic_address_type, party_id"
			+ " FROM CMX_ORS.C_PARTY_ELECT_AD"
			//+ " WHERE electronic_address_type = :electronicAddressType"
			+ " WHERE party_id = :partyId";
	
	/*public static final String PARTY_LIST_CRM_ID_SEARCH_QUERY = "SELECT rowid_object, pkey_src_object"
			+ " FROM mv_pkg_ws_party_crm_id"
			+ " WHERE rowid_object IN (:partyIds)";*/
	public static final String PARTY_LIST_CRM_ID_SEARCH_QUERY = "SELECT C_PARTY.ROWID_OBJECT rowid_object,"
			+ " C_PARTY_XREF.PKEY_SRC_OBJECT pkey_src_object"
			+ " 	  FROM CMX_ORS.C_PARTY C_PARTY"
			+ " 	 INNER JOIN CMX_ORS.C_PARTY_XREF C_PARTY_XREF"
			+ " 	    ON C_PARTY.ROWID_OBJECT = C_PARTY_XREF.ROWID_OBJECT"
			+ " 	 INNER JOIN CMX_ORS.C_REPOS_SYSTEM C_REPOS_SYSTEM"
			+ " 	    ON C_PARTY_XREF.ROWID_SYSTEM = C_REPOS_SYSTEM.ROWID_SYSTEM"
			+ " 	 WHERE C_REPOS_SYSTEM.SYSTEM_NAME = 'CRM'"
			+ " 	   AND C_PARTY.rowid_object IN (:partyIds)"
				;
	
	/*public static final String PARTY_CRM_ID_SEARCH_QUERY = "SELECT rowid_object, pkey_src_object"
			+ " FROM mv_pkg_ws_party_crm_id"
			+ " WHERE rowid_object = :partyId";*/
	public static final String PARTY_CRM_ID_SEARCH_QUERY = "SELECT C_PARTY.ROWID_OBJECT rowid_object,"
			+ " C_PARTY_XREF.PKEY_SRC_OBJECT pkey_src_object"
			+ " 	  FROM CMX_ORS.C_PARTY C_PARTY"
			+ " 	 INNER JOIN CMX_ORS.C_PARTY_XREF C_PARTY_XREF"
			+ " 	    ON C_PARTY.ROWID_OBJECT = C_PARTY_XREF.ROWID_OBJECT"
			+ " 	 INNER JOIN CMX_ORS.C_REPOS_SYSTEM C_REPOS_SYSTEM"
			+ " 	    ON C_PARTY_XREF.ROWID_SYSTEM = C_REPOS_SYSTEM.ROWID_SYSTEM"
			+ " 	 WHERE C_REPOS_SYSTEM.SYSTEM_NAME = 'CRM'"
			+ " 	   AND C_PARTY.rowid_object = :partyId";
	
	/*public static final String PARTY_LIST_LOGEMENT_ID_SEARCH_QUERY = "SELECT rowid_object, pkey_src_object"
			+ " FROM mv_pkg_ws_party_logement_id"
			+ " WHERE rowid_object IN (:partyIds)";*/
	public static final String PARTY_LIST_LOGEMENT_ID_SEARCH_QUERY = "SELECT c_party.rowid_object rowid_object,"
			+ " 			c_party_xref.pkey_src_object pkey_src_object"
			+ " 	  FROM CMX_ORS.c_party c_party"
			+ " 	 INNER JOIN CMX_ORS.c_party_xref c_party_xref"
			+ " 	    ON c_party.rowid_object = c_party_xref.rowid_object"
			+ " 	 INNER JOIN CMX_ORS.c_repos_system c_repos_system"
			+ " 	    ON c_party_xref.rowid_system = c_repos_system.rowid_system"
			+ " 	 WHERE c_repos_system.system_name = 'Logement'"
			+ " 	   AND c_party.rowid_object IN (:partyIds)";
	
	/*public static final String PARTY_LOGEMENT_ID_SEARCH_QUERY = "SELECT rowid_object, pkey_src_object"
			+ " FROM mv_pkg_ws_party_logement_id"
			+ " WHERE rowid_object = :partyId";*/
	public static final String PARTY_LOGEMENT_ID_SEARCH_QUERY = "SELECT c_party.rowid_object rowid_object,"
			+ " 			c_party_xref.pkey_src_object pkey_src_object"
			+ " 	  FROM CMX_ORS.c_party c_party"
			+ " 	 INNER JOIN CMX_ORS.c_party_xref c_party_xref"
			+ " 	    ON c_party.rowid_object = c_party_xref.rowid_object"
			+ " 	 INNER JOIN CMX_ORS.c_repos_system c_repos_system"
			+ " 	    ON c_party_xref.rowid_system = c_repos_system.rowid_system"
			+ " 	 WHERE c_repos_system.system_name = 'Logement'"
			+ " 	   AND c_party.rowid_object = :partyId";
	
	/*public static final String PARTY_SERVICE_ID_SEARCH_QUERY = "SELECT rowid_object, pkey_src_object"
			+ " FROM mv_pkg_ws_party_service_id"
			+ " WHERE rowid_object = :partyId";*/
	public static final String PARTY_SERVICE_ID_SEARCH_QUERY = "SELECT C_PARTY.ROWID_OBJECT rowid_object,"
			+ " 			C_PARTY_XREF.PKEY_SRC_OBJECT pkey_src_object"
			+ " 	  FROM CMX_ORS.C_PARTY C_PARTY"
			+ " 	 INNER JOIN CMX_ORS.C_PARTY_XREF C_PARTY_XREF"
			+ " 	    ON C_PARTY.ROWID_OBJECT = C_PARTY_XREF.ROWID_OBJECT"
			+ " 	 INNER JOIN CMX_ORS.C_REPOS_SYSTEM C_REPOS_SYSTEM"
			+ " 	    ON C_PARTY_XREF.ROWID_SYSTEM = C_REPOS_SYSTEM.ROWID_SYSTEM"
			+ " 	 WHERE C_REPOS_SYSTEM.SYSTEM_NAME = 'Service'"
			+ " 	 AND C_PARTY.rowid_object = :partyId";
	 
	/**
	 * SELECT contract number and contract office from partyId.
	 */
	public static final String PARTY_LIST_CONTRACT_SEARCH_QUERY = 
			"SELECT contract.numero_contrat, contract.contract_office, party_contract.party_id"
			+ " FROM CMX_ORS.c_party_contract_rel party_contract"
			+ " INNER JOIN CMX_ORS.c_contract contract ON party_contract.contract_id = contract.rowid_object"
			+ " WHERE party_contract.party_id IN (:partyIds)";
	
	/**
	 * SELECT contract number and contract office from partyId.
	 */
	public static final String PARTY_CONTRACT_SEARCH_QUERY = "SELECT contract.numero_contrat, contract.contract_office, party_contract.party_id"
			+ " FROM CMX_ORS.c_party_contract_rel party_contract"
			+ " INNER JOIN CMX_ORS.c_contract contract ON party_contract.contract_id = contract.rowid_object"
			+ " WHERE party_contract.party_id = :partyId";
}
